import React, {FC, useEffect, useState} from "react";
import TextArea from "antd/es/input/TextArea";
import {Button, Radio, RadioChangeEvent, Space, Spin, Table, Tabs, Tree} from "antd";
import {datagetpath, ISqltestProps} from "./Ireacttest";
import axios from "axios";
import 'antd/dist/antd.css';
import {stringify} from "querystring";
import qs from "qs";
import {Editor} from "./Editor";
import {Controlled} from "react-codemirror2";
import {global} from "./Ireacttest";
import {importsExcel} from "./excel_operater";
import * as XLSX from 'xlsx';
import {CSVDownload, CSVLink} from 'react-csv';


export const Sqltest:FC<ISqltestProps>= props=> {

    const [list, setList] = useState(props.excellist);
    const TabPane = Tabs.TabPane;
    const [sqlstr, setsqlstr] = useState(props.sqlstr);
    const [tabledata, settabledata] = useState(props.tabledata);
    const [tables,settables]=useState(props.tables);
    const [ctreenode,setctreenode]=useState("");
    const [loading, setLoading] = useState(false);
    const [loaddataing, setloaddataing] = useState(false);

    const [rdvalue, setrdValue] = useState("newsql");
    const [checkedKeys, setCheckedKeys] = useState<React.Key[]>(['0-0-0']);

    const onrdChange = (e: RadioChangeEvent) => {
        console.log('radio checked', e.target.value);
        setrdValue(e.target.value);
    };

    const onCheck = (checkedKeysValue: any) => {
        //console.log('onCheck', checkedKeysValue);
        setCheckedKeys(checkedKeysValue)
    };

    /*
    病案评分标准，导出SQL语句
    * */
    function importExcel() {
      // let resault=  importsExcel("d:\\f1.xls");
       // let resault=XLSX.readFile("D:\\f1.xls",{ type: 'binary', cellDates: true });
        let datas=  tabledata.data;
       // console.log(datas);
        let strval="delete from T_MEDICAL_MARK_NEW;";
        let tempsqlstr="";
        tempsqlstr=strval;
        for(let i=0;i<datas.length;i++)
        {
            let id:string=(i+1).toString();
            strval="insert into T_MEDICAL_MARK_NEW(ID,CODE,NAME,DEDUCT_STAND,"+
                "DEDUCT_SCORE,ISSINGVETO,ISMODIFY_MANUAL,VALID_STATE,"+
                "SPELL_CODE,TYPE,TYPESTR)values("+id+","+id+",'"+datas[i].GRADE_ITEM+"','"+
                datas[i].GRADE_ITEM+"','"+datas[i].SCORE+
                "','N','Y','Y','','Y','"+datas[i].TYPE+"')";
            tempsqlstr=tempsqlstr+"\n\r"+strval+";";
        }
        setsqlstr(tempsqlstr);
    }

    function onSelect(info:any) {
       // alert(info.toString());
        setctreenode(info.toString());
    }

    function onDoubleClick(eventinfo:any,info:any) {
       let val=sqlstr;

        // <Radio value={"newsql"}>生成SQL查询</Radio>
        // <Radio value={"oldsql"}>现有SQL查询拼接</Radio>
        // <Radio value={"expdbselect"}>选中表导出</Radio>
        // <Radio value={"expdbunselect"}>排除选中表导出</Radio>

       if(rdvalue=="newsql")
       {
           //生成SQL查询
           setsqlstr("select * from "+info.key.toString());
       }
       else if(rdvalue=="oldsql")
       {
           //现有SQL查询拼接
           setsqlstr(val+info.key.toString());
       }
       else if(rdvalue=="expdbselect")
       {
           //选中表导出
           let tablesstr:string="";
           for(let i=0;i<checkedKeys.length;i++)
           {
               if(tablesstr=="")
               {
                   tablesstr=checkedKeys[i].toString();
               }
               else
               {
                   tablesstr=tablesstr+","+checkedKeys[i].toString();
               }
           }
           let expdpstr="expdp newdoc/new123456@ORCL directory=EXP_DIR dumpfile=xxxx.dmp tables="+tablesstr
           setsqlstr(expdpstr);

       }
       else if(rdvalue=="expdbselect")
        {
            //选中表导出
            let tablesstr:string="";
            for(let i=0;i<checkedKeys.length;i++)
            {
                if(tablesstr=="")
                {
                    tablesstr=checkedKeys[i].toString();
                }
                else
                {
                    tablesstr=tablesstr+","+checkedKeys[i].toString();
                }
            }
            let expdpstr="expdp newdoc/new123456@ORCL directory=EXP_DIR dumpfile=xxxx.dmp tables="+tablesstr
            setsqlstr(expdpstr);

        }
        else if(rdvalue=="expdbunselect")
       {
           //排除选中表导出
           let tablesstr:string="";
           for(let i=0;i<tables.length;i++)
           {
               let isexit:boolean=false;
               for(let j=0;j<checkedKeys.length;j++)
               {
                  if(checkedKeys[j].toString()==tables[i].key)
                  {
                      isexit=true;
                      break;
                  }
               }
               if(!isexit) {
                   if (tablesstr == "") {
                       tablesstr = tables[i].key;
                   } else {
                       tablesstr = tablesstr + "," + tables[i].key;
                   }
               }
           }
           let expdpstr="expdp newdoc/new123456@ORCL directory=EXP_DIR dumpfile=xxxx.dmp tables="+tablesstr
           setsqlstr(expdpstr);
       }
       else
       {
           setsqlstr("select * from "+info.key.toString());
       }
       //setsqlstr(val+info.key.toString());
    }

    function clearaction() {
        setsqlstr("");
    }

    function run_key(e:any,e2:any) {
        if(e2.keyCode==120)
        {
            //回车13
            //alert("F5")
            queryaction();
        }
    }

    //获取数据库所有的表
    function loadtables() {
        setLoading(true)
        axios.post(datagetpath, qs.stringify({
            sql: "SELECT table_name FROM USER_TABLES"
        }), {
            headers: {
                'Content-Type': 'application/x-www-form-urlencoded;charset=UTF-8' //'multipart/form-data', 'application/json;charset=UTF-8'
            },
        }).then(res => {
            let tables: any = [];
            for(let i=0;i<res.data.length;i++)
            {
                let val=res.data[i].TABLE_NAME;
                tables.push({title:val,key:val});
            }
            console.log(tables);

            settables(tables);


        }).catch(e => {
            alert("查询出错,原因：" + e.message);
            settabledata({
                data: null,
                colums: null
            })
        }).finally(()=>{
            setLoading(false);
        })
    }

    function queryaction() {

        if (sqlstr.trim() == "") {
            alert("请先输入SQL语句！");
            return;
        }

        setloaddataing(true);
        axios.post(datagetpath, qs.stringify({
            sql: sqlstr
        }), {
            headers: {
                //     'Access-Control-Allow-Origin': '*',
                //     // 'Access-Control-Allow-Methods': '*',
                //     // "Access-Control-Allow-Headers": "Content-Type,XFILENAME,XFILECATEGORY,XFILESIZE",
                'Content-Type': 'application/x-www-form-urlencoded;charset=UTF-8' //'multipart/form-data', 'application/json;charset=UTF-8'
            },
        }).then(res => {
            let keynames = Object.keys(res.data[0]);
            let colsset: any = [];
            for (let i = 0; i < keynames.length; i++) {
                let kname = keynames[i];
                colsset.push({
                    title: kname,
                    dataIndex: kname,
                    key: kname,
                })
            }
            settabledata({
                data: res.data,
                colums: colsset
            })

        }).catch(e => {
            alert("查询出错,原因：" + e.message);
            settabledata({
                data: null,
                colums: null
            })
        }).finally(()=>{
            setloaddataing(false);
        })

    }

    useEffect(() => {
        global.pagescout=global.pagescout+1;
        document.title="sql测试"+global.pagescout.toString();
        loadtables();
    }, [])

    // @ts-ignore
    // @ts-ignore
    return <div>
        <div style= {{width: '100%'}}>
            <hr />
            <table>
                <tr>
                    <td><strong>参数设置：</strong></td>
                    {/*<td><Button type={'primary'} onClick={loadtables}>用户表</Button></td>*/}
                    <td>
                        <Radio.Group onChange={onrdChange} value={rdvalue}>
                            <Radio value={"newsql"}>生成SQL查询</Radio>
                            <Radio value={"oldsql"}>现有SQL查询拼接</Radio>
                            <Radio value={"expdbselect"}>选中表导出</Radio>
                            <Radio value={"expdbunselect"}>排除选中表导出</Radio>
                        </Radio.Group>
                    </td>
                    <td><Button type={'primary'} size={"small"} onClick={queryaction}>执行</Button></td>
                    <td><Button type={'primary'} size={"small"}  onClick={clearaction}>清空</Button></td>
                    <td><Button type={'primary'} size={"small"}>
                        <CSVLink data={tabledata.data} target="_blank" filename={"tempdatafile.cvs"}>
                            结果导出EXCEL
                        </CSVLink>
                    </Button></td>
                    <td><a href={"http://127.0.0.1:3060?id=0"} target={"_blank"}>新建查询</a></td>
                    <td>
                        {/*<CSVDownload*/}
                        {/*    data={tabledata.data} //要导出的数据list*/}
                        {/*    target="_blank" //以打开新标签页形式进行下载*/}
                        {/*    filename="tempdata.csv"*/}
                        {/*    style={{ float: 'right', marginTop: '-10px', marginRight: '5px' }}*/}
                        {/*    headers={tabledata.colums}*/}
                        {/*>*/}
                        {/*    下载*/}
                        {/*</CSVDownload>*/}


                    </td>
                </tr>
            </table>
            <hr/>
        </div>
        <div style={{width: '70%',height:'1000px',float:"left"}} className="card-container">

            <Tabs type="card" style={{width:'100%', height:'220px'}}>
                <TabPane tab="查询操作" key="1">
            <Editor
                language='text/x-mysql'
                displayName='blackboard'
                value={sqlstr}
                onChange={setsqlstr}
                onKeydown={run_key}
            />
                </TabPane>
            </Tabs>
            <div style={{width:'100%'}}>
                执行结果：
                <hr />
            </div>
            <div style={{width: '100%', height: 'auto',overflow:"auto"}}>
                <Spin spinning={loaddataing}>
                    <Table bordered dataSource={tabledata.data} columns={tabledata.colums}
                           pagination={{pageSize: 5}}
                           scroll={{x:1300}}
                    />
                </Spin>
            </div>
        </div>
        <div style={{width: '30%',height:'800px',float:"right"}} className="card-container">
        <Tabs type="card">
            <TabPane tab="数据库表集合" key="1">
                <Spin spinning={loading} >
                    <Tree treeData={tables}
                          checkable
                          style={{height:'440px',overflow:'auto'}}
                          onCheck={onCheck}
                          onSelect={onSelect} onDoubleClick={onDoubleClick}
                          checkedKeys={checkedKeys}
                    >
                    </Tree>
                </Spin>
            </TabPane>
        </Tabs>
        </div>

    </div>
};